package com.taomeng.dao.impl;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.taomeng.dao.StudentDao;
import com.taomeng.entity.LeaveApplicationInfo;
import com.taomeng.entity.PageBean;
import com.taomeng.entity.Student;
import com.taomeng.util.JdbcTemplateSupport;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Map;

@Repository
public class StudentDaoImpl implements StudentDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private JdbcTemplateSupport jdbcTemplateSupport;

    @Override
    public Student findByStudentNum(String studentNum) {
        /*
        select   c.num,	d.name from class c,department d where	c.departmentId = d.id;
        * */

        String sql = "select * from student where num = ?";
        Object[] params = {studentNum};
        return jdbcTemplate.queryForObject(sql, params, new BeanPropertyRowMapper<>(Student.class));
    }

    @Override
    public Map<String, Object> findAllInfoByStudentNum(String studentNum) {
        String sql = "select " +
                "* " +
                "from " +
                "student t1, " +
                "(select " +
                "c.id classId, c.num classNum, " +
                "d.`name` departmentName " +
                "from " +
                "class c, " +
                "department d " +
                "where " +
                "c.departmentId = d.id) t2 " +
                "where  " +
                "t1.classId=t2.classId " +
                "and " +
                "num = ?";
        Object[] params = {studentNum};
        Map<String, Object> map = jdbcTemplate.queryForMap(sql, params);
        return map;
    }

    @Override
    public PageBean<Student> findByPage(int page, int limit, String key, Long departmentId, Long userId) {
        StringBuilder sb = new StringBuilder("select " +
                "t1.id id,t1.name name,t1.admitId admitId,t1.num num,t2.num classNum,t1.sex sex,t1.dormitory dormitory " +
                "from student t1 left join class t2 " +
                "on t1.classId = t2.id " +
                "left join department t3 " +
                "on t3.id = t2.departmentId  " +
                "left join " +
                "t_user t4 " +
                "on t2.banZhuRenId = t4.id " +
                "where 1 = 1 ");
        Page<Student> pagination = PageHelper.startPage(page, limit);


        try {
            if (null != key) {
                key = "%" + key + "%";
                sb.append(" and t1.name like ").append(key)
                        .append(" or t1.admitId like ").append(key)
                        .append(" or t1.num like ").append(key)
                        .append(" or t2.num like ").append(key)
                        .append(" or t1.dormitory like ").append(key);
            }
            if (null != departmentId) {
                sb.append(" and t3.id = ").append(departmentId);
            } else if (null != userId) {
                sb.append(" and t4.id = ").append(userId);
            }
            return jdbcTemplateSupport.queryForPage(
                    sb.toString(),
                    pagination,
                    new BeanPropertyRowMapper<>(Student.class));
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int add(Student student) {
        String sql = "insert into student values (null,?,?,?,?,?,?,?)";
        Object[] params = {
                student.getName(),
                student.getAdmitId(),
                student.getNum(),
                student.getPassword(),
                student.getClassNum(),
                student.getSex(),
                student.getDormitory()
        };
        try {
            return jdbcTemplate.update(sql, params);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delStudentById(int id) {
        String sql = "delete from student where id = ? ";
        Object[] params = {
                id
        };

        try {
            return jdbcTemplate.update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public boolean delBatchAll(String ids) {
        String sql = "delete from student where id = ?";

        ArrayList<Object[]> list = new ArrayList<>();
        for (String id : ids.split(",")) {
            Object[] o = {
                    id
            };
            list.add(o);
        }

        try {
            jdbcTemplate.batchUpdate(sql, list);
            return true;
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public int updateStudent(Student student) {
        String sql = "update student set name = ? , admitId= ? ,num = ? ,password=? ,classId= ?,sex=?, dormitory=? where id = ?";
        Object[] params = {
                student.getName(),
                student.getAdmitId(),
                student.getNum(),
                student.getPassword(),
                student.getClassNum(),
                student.getSex(),
                student.getDormitory(),
                student.getId()
        };
        try {
            return jdbcTemplate.update(sql, params);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
